package cn.lystudio.rohmtimeopwide_background_v11.mapper;

import cn.lystudio.rohmtimeopwide_background_v11.entity.po.RecordPO;
import cn.lystudio.rohmtimeopwide_background_v11.entity.vo.OpUserFullNameVO;
import cn.lystudio.rohmtimeopwide_background_v11.entity.vo.TimeRecord4ExcelVO;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
@Mapper
public interface RecordPOMapper extends BaseMapper<RecordPO> {
	@Select("SELECT res.*\n" +
			"FROM (\n" +
			"\tSELECT t1.recordTimeBeginDT as recordTimeDT,\n" +
			"\t\tuz.userFullName as userFullName,\n" +
			//"\t\tt1.recordName as recordName,\n" +
			"\tIF(\n" +
			"\t\tt1.recordLotInfo IS NULL OR t1.recordLotInfo='',\n" +
			"\t\tt1.recordName,\n" +
			"\t\tCONCAT( t1.recordName, ' ,Lot:', t1.recordLotInfo )\n" +
			"\t) AS recordName, \t\n" +
			"\t\t IF(t1.recordRemark IS NULL OR t1.recordRemark ='',t1.recordResult,CONCAT(t1.recordResult, ' ,备注:' , t1.recordRemark)) AS recordResult \n" +
			"\tFROM t_record t1\n" +
			"\tLEFT JOIN t_users uz on t1.userCode= uz.userCode\n" +
			"\tWHERE t1.machineNo=#{machineNo}\n" +
			"\t\tAND t1.recordTimeBeginDT>=#{beginDate}\n" +
			"\t\tAND t1.recordTimeBeginDT<#{endDate}\n" +
			"\tUNION ALL \n" +
			"\tSELECT t2.recordTimeEndDT as recordTimeDT,\n" +
			"\t\tuz.userFullName as userFullName,\n" +
			"\t\tt2.recordName  as recordName,\n" +
			"\t\tCONCAT(t2.recordResult, ' 结束 ↑') as recordResult\n" +
			"\tFROM t_record t2\n" +
			"\tLEFT JOIN t_users uz on t2.userCode= uz.userCode\n" +
			"\tWHERE t2.machineNo=#{machineNo}\n" +
			"\t\tAND t2.recordTimeEndDT>=#{beginDate}\n" +
			"\t\tAND t2.recordTimeEndDT<#{endDate}\n" +
			"\t\t AND TIMESTAMPDIFF(MINUTE,t2.recordTimeBeginDT,t2.recordTimeEndDT)>5  \n" +
			" UNION ALL \n" +
			"SELECT t3.recordTimeDT as recordTimeDT,\n" +
			"t3.userFullName as userFullName,\n" +
			"t3.recordName as recordName,\n" +
			"t3.recordResult as recordResult  \n" +
			"FROM t_from_rohm_record t3\n" +
			"\tWHERE t3.machineNo=#{machineNo}\n" +
			"\t\tAND t3.recordTimeDT>=#{beginDate}\n" +
			"\t\tAND t3.recordTimeDT<#{endDate}\n" +
			") res\n" +
			"ORDER BY res.recordTimeDT")
	List<TimeRecord4ExcelVO> getTimeRecord4ExcelVO(@Param("machineNo")String machineNo, @Param("beginDate")String beginDate, @Param("endDate")String endDate);

	//@Select("SELECT userFullName,\n" +
	//		"COUNT(*) as count\n" +
	//		"FROM t_from_rohm_record\n" +
	//		"WHERE machineNo=#{machineNo}\n" +
	//		"AND recordTimeDT>=#{beginDate}\n" +
	//		"AND recordTimeDT<#{endDate}\n" +
	//		"GROUP BY userFullName\n" +
	//		"ORDER BY count DESC\n" +
	//		"LIMIT 1")
	@Select("SELECT res.userFullName userFullName,\n" +
			"COUNT(*) count\n" +
			"FROM (\n" +
			"\tSELECT\n" +
			"\t\trohm.userFullName \n" +
			"\tFROM\n" +
			"\t\tt_from_rohm_record rohm \n" +
			"\tWHERE\n" +
			"\t\trohm.machineNo = #{machineNo} \n" +
			"\t\tAND rohm.recordTimeDT >= #{beginDate} \n" +
			"\t\tAND rohm.recordTimeDT < #{endDate} \n" +
			"\tUNION ALL\n" +
			"\tSELECT\n" +
			"\t\tusz.userFullName \n" +
			"\tFROM\n" +
			"\t\tt_record rec\n" +
			"\t\tINNER JOIN t_users usz ON rec.userCode = usz.userCode \n" +
			"\tWHERE\n" +
			"\t\trec.machineNo = #{machineNo} \n" +
			"\t\tAND (\n" +
			"\t\t\t( rec.recordTimeBeginDT >= #{beginDate} AND rec.recordTimeBeginDT < #{endDate}) \n" +
			"\t\t\tOR ( rec.recordTimeEndDT >= #{beginDate} AND rec.recordTimeEndDT < #{endDate} ) \n" +
			"\t\t) \n" +
			"\t) res\n" +
			"GROUP BY\n" +
			"\tres.userFullName \n" +
			"ORDER BY\n" +
			"\tcount DESC \n" +
			"LIMIT 1")
	OpUserFullNameVO getOpUserFullName(@Param("machineNo")String machineNo, @Param("beginDate")String beginDate, @Param("endDate")String endDate);
}
